Exercises and Solutions
CHAPTER 2:
Exercise 4 (copyprog.p)
1.	Open “copyprog.p”.
2.	Using the Field Selector screen, add the legal_n, dba, tax_str, tax_cty, tax_st and tax_zip to the DISPLAY statement.  These fields will need to be added BEFORE the “WITH 1 COLUMN” phrase.
3.	Save “copyprog.p”.
Solution:
FOR EACH admin NO-LOCK:
    DISPLAY unum rnum legal_n dba tax_str tax_cty taxst tax_zip WITH 1 COLUMN.
END.

CHAPTER 3:
Exercise 1 (foreach1.p)
1.	Using the ADMIN table, read each record and display the account number (unum, rnum), legal name (legal_n), city (tax_cty) and zip code (tax_zip).
Note:  Make sure to include the END statement.
2.	Check the syntax and run the program.
3.	Save the program as “foreach1.p”.
Solution:
FOR EACH admin NO-LOCK:
    DISPLAY unum rnum legal_n tax_cty tax_zip.
END.    

Exercise 2 (foreach2.p)
1.	Modify the program from Exercise 1 to select records whose initial liability date (init_liab_dte) is after (>) 12/31/2002.  To check your query, add init_liab_dte to the display statement.
2.	Check the syntax and run the program.
3.	Save the program as “foreach2.p”.
Solution:
FOR EACH admin WHERE init_liab_dte > 12/31/2002 NO-LOCK:
    DISPLAY unum rnum legal_n tax_cty tax_zip init_liab_dte.
END.    

Exercise 3 (foreach3.p)
1.	Modify the program from Exercise 2 to sort retrieved records by initial liability date in descending order.
2.	Check the syntax and run the program.
3.	Save the program as “foreach3.p”.
Solution:
FOR EACH admin WHERE init_liab_dte > 12/31/2002 NO-LOCK BY init_liab_dte descending:
    DISPLAY unum rnum legal_n tax_cty tax_zip init_liab_dte.
END.    

Exercise 4 (find1.p)
1.	Using the FIND command, retrieve the last record from the ADMIN table whose initial liability date is after 12/31/2003.  If you need to reference the database definition for the ADMIN table, use EDITINSERT FIELDS.
2.	Display the account number, name, and initial liability date.
3.	Check the syntax and run the program.
4.	Save the program as “find1.p”.
Solution:
FIND LAST admin WHERE init_liab_dte > 12/31/2003 NO-LOCK.
    DISPLAY unum rnum legal_n init_liab_dte.

Exercise 5 (findqofa.p)
1.	Use the FOR EACH command to retrieve all records from the ADMIN table whose initial liability date is after 12/31/2003.
2.	Within the FOR EACH loop, FIND the most recent QUARTERLY record having the same account number as the selected ADMIN record.
3.	Display the account number, year, quarter, name, initial liability date, status code (status_cd), and all 3 months of employment.  If you need to reference the database definition for the tables, use EDITINSERT FIELDS.
4.	Note:  Remember to qualify unum and rnum with a table name, since they are common to both tables.
5.	Check the syntax and run the program.
6.	Save the program as “findqofa.p”.
Solution:
FOR EACH admin WHERE init_liab_dte > 12/31/2003 NO-LOCK:
    FIND FIRST quarterly OF admin NO-LOCK.
        DISPLAY admin.unum admin.rnum yr qtr legal_n init_liab_dte status_cd 
                m1emp m2emp m3emp.
END. /* for each admin */

Exercise 6 (findwnoerr.p)
1.	Modify the program you created in Exercise 5 to use the NO-ERROR option.  Search for the quarterly record using the WHERE clause rather than the OF phrase.
2.	Use the IF AVAILABLE phrase to check for the existence of the record before issuing the DISPLAY statement.
3.	Check the syntax and run the program.
4.	Save the program as “findwnoerr.p”.
Solution:
FOR EACH admin WHERE init_liab_dte > 12/31/2003 NO-LOCK:
    FIND quarterly WHERE quarterly.unum = admin.unum AND
                         quarterly.rnum = admin.rnum AND
                         quarterly.yr = 2004 AND
                         quarterly.qtr = 1 NO-LOCK NO-ERROR.
    IF AVAILABLE quarterly THEN                     
        DISPLAY admin.unum admin.rnum yr qtr legal_n init_liab_dte status_cd 
                m1emp m2emp m3emp.    
END. /* for each admin */

CHAPTER 5:
Exercise 1 (ifbasic.p)
1.	Read all of the quarterly records.
2.	If the status code is 1, display “Active” and appropriate fields from the quarterly table.  Otherwise, display “Inactive or Pending” and appropriate fields from the quarterly table.
3.	Check the syntax and run the program.
4.	Save the program as “ifbasic.p”.
Solution:
FOR EACH quarterly NO-LOCK:
    IF status_cd = "1" THEN
        DISPLAY "Active" unum rnum yr qtr.
    ELSE
        DISPLAY "Inactive or Pending" unum rnum yr qtr.
END.

Exercise 2 (ifnest.p)
1.	Read the admin table for all records with an initial liability date after 09/30/2003.
2.	Find the related quarterly record for 2004-1, using the where clause.
3.	If the quarterly is available, check the meei code.  If it is 1, 4 or 6, display the account number and “Single”.  If it is 2, display the account number and “Master”.  Otherwise, display the account number and “Subunit”.
4.	If it is not available, display the account number and “No Quarterly for 2004-1”.
5.	Check the syntax and run the program.
6.	Save the program as “ifnest.p”.
Solution:
FOR EACH admin WHERE init_liab_dte > 09/30/2003 NO-LOCK:
    FIND quarterly WHERE quarterly.unum = admin.unum and
                         quarterly.rnum = admin.rnum and
                         quarterly.yr = 2004 and
                         quarterly.qtr = 1 NO-LOCK NO-ERROR.
    IF AVAILABLE quarterly THEN DO:
        IF meei_cd = 1 or meei_cd = 4 or meei_cd = 6 THEN
            DISPLAY quarterly.unum quarterly.rnum "Single".
        ELSE DO:
            IF meei_cd = 2 THEN
                DISPLAY quarterly.unum quarterly.rnum "Master".
            ELSE
                DISPLAY quarterly.unum quarterly.rnum "Subunit".
        END.  /* else do */        
    END.  /* if available quarterly */
    ELSE
        DISPLAY skip admin.unum admin.rnum "No Quarterly for 2004-1".
END. /* for each admin */

CHAPTER 6:
Exercise 1 (defvar.p)
Create a program that will count all of the open status quarterly records for 2004-3 and accumulate the total employment for the quarter.  Exclude subunits.

1.	Define variables to hold the record count and the total employment.
2.	Read the quarterly table for all 2004-3 records that are not subunits.
3.	Check for an open status, add the employment numbers to the total employment and increment the record counter by 1.
4.	At the end of the program, display the total record count and total employment.
5.	Check the syntax and run the program.
6.	Save the program as “defvar.p”.
Solution:
def var rec-cnt as integer no-undo.
def var tot-emp as integer no-undo.
FOR EACH quarterly WHERE yr = 2004 and qtr = 3 and rnum = 0 NO-LOCK:
    IF status_cd = "1" THEN
        ASSIGN tot-emp = tot-emp + (m1emp + m2emp + m3emp)
               rec-cnt = rec-cnt + 1.
END.
DISPLAY "Total Employment" tot-emp " # of Records" rec-cnt.

Exercise 2 (defbuf.p)
Create a program to compare the county code from 2004-1 with the county code from 2003-4 for open accounts.  If it has changed, display the account number and the old and new county codes.

1.	Define a record buffer for the quarterly table to hold the previous quarter’s information.
2.	Read all of the 2004-1 records in the quarterly file.
3.	If the account is open, find the corresponding record for 2003-4.
4.	Compare the 2004-1 county code to the 2003-4 county code.  If they are not the same (not equal, <>), display the information.
5.	Check the syntax and run the program.
6.	Save the program as “defbuf.p”. 
Solution:
def buffer prevqtr for quarterly.
FOR EACH quarterly WHERE yr = 2004 and qtr = 1 NO-LOCK:
    IF status_cd = "1" THEN DO:
        FIND prevqtr WHERE prevqtr.unum = quarterly.unum and
                           prevqtr.rnum = quarterly.rnum and
                           prevqtr.yr = 2003 and 
                           prevqtr.qtr = 4 NO-LOCK NO-ERROR.
        IF AVAILABLE prevqtr THEN
            IF quarterly.county_cd <> prevqtr.county_cd THEN
                DISPLAY quarterly.unum quarterly.rnum quarterly.county_cd prevqtr.county_cd.
    END.
END.

CHAPTER 7:
Exercise 1 (dispcol.p)
Create a program that will read the records from the admin table and display the fields from the first 10 records on the screen in a single column.

1.	Define a variable to count the records.
2.	Read the admin table.
3.	Display the record if less than 11 records have been displayed.
4.	Check the syntax and run the program.
5.	Save the program as “dispcol.p”.
Solution:
DEF VAR rec-cnt AS INTEGER NO-UNDO.
FOR EACH admin NO-LOCK:
    ASSIGN rec-cnt = rec-cnt + 1.
    IF rec-cnt < 11 THEN
        DISPLAY admin WITH 1 COLUMN.
END.    

The solution produces the following error message.  The e‑mail field is too lengthy to display in the frame.  You will need to Character Format the field(s) to shorten the display length.  See next page for solution.

Solution: (dispcol2.p)
DEF VAR rec-cnt AS INTEGER NO-UNDO.
FOR EACH admin NO-LOCK:
    ASSIGN rec-cnt = rec-cnt + 1.
    IF rec-cnt < 11 THEN
        DISPLAY admin.addr-source 
        admin.ar-ind 
        admin.area_cd 
        admin.ars-desc format "x(35)" 
        admin.ars-percentage 
        admin.ars-sequence 
        admin.buyer-name 
        admin.ces-ind 
        admin.chkdig 
        admin.coll-status-id 
        admin.consent-cd 
        admin.consent-end-yr 
        admin.consent-init-yr 
        admin.contact 
        admin.contact-title 
        admin.csm-dte 
        admin.csm-ind 
        admin.dba 
        admin.delete-date 
        admin.e-mail format "x(35)" 
        admin.edit-dte 
        admin.edit_flag 
        admin.ein 
        admin.end_liab_dte 
        admin.event-com-id 
        admin.event-oth-com 
        admin.f-suppl-ps 
        admin.fax_area_cd 
        admin.fax_phone 
        admin.ffd-a 
        admin.ffd-a-rc 
        admin.ffd-b 
        admin.ffd-b-rc 
        admin.ffd-county 
        admin.ffd-county-rc 
        admin.ffd-emp 
        admin.ffd-emp-rc 
        admin.ffd-multisingle-rc 
        admin.ffd-multsingle 
        admin.ffd-naics 
        admin.ffd-naics-rc 
        admin.ffd-notify-yr 
        admin.ffd-pla 
        admin.ffd-pla-rc 
        admin.ffd-review-yr 
        admin.ffd-wage 
        admin.ffd-wage-rc 
        admin.geo-county 
        admin.geosoftware 
        admin.geosource-cd 
        admin.incl-subset 
        admin.incl_equi 
        admin.init_liab_dte 
        admin.input_dte 
        admin.latitude 
        admin.legal_n 
        admin.ll-chg-qtr 
        admin.ll-chg-yr 
        admin.location-cd 
        admin.longitude 
        admin.mailst 
        admin.mail_cty 
        admin.mail_ext 
        admin.mail_st1 
        admin.mail_st2 
        admin.mail_zip 
        admin.match-cd 
        admin.max_run 
        admin.mo_addr_ind 
        admin.multi-pred-ind 
        admin.multi-succ-ind 
        admin.mwr-id 
        admin.mwr-mail-ind 
        admin.mwr-pass 
        admin.mwr-rnum 
        admin.mwrunit-desc FORMAT "X(35)" 
        admin.mwr_received 
        admin.m_area_cd 
        admin.m_phone 
        admin.newunit-id 
        admin.old-geosoftware 
        admin.old-geosource-cd 
        admin.old-latitude 
        admin.old-ll-chg-qtr 
        admin.old-ll-chg-yr 
        admin.old-location-cd 
        admin.old-longitude 
        admin.old-match-cd 
        admin.old_ui_addr_ind 
        admin.org_type_cd 
        admin.peo-ein 
        admin.phone 
        admin.Phon_ext 
        admin.physst 
        admin.phys_cty 
        admin.phys_ext 
        admin.phys_str 
        admin.phys_str2 
        admin.phys_zip 
        admin.plac-dte 
        admin.pred-source 
        admin.prednum 
        admin.predrun 
        admin.print-flag 
        admin.ps-trans-dte 
        admin.react_dte 
        admin.rnum 
        admin.run_desc 
        admin.s-suppl-ps 
        admin.season 
        admin.sfd-a 
        admin.sfd-a-rc 
        admin.sfd-b 
        admin.sfd-b-rc 
        admin.sfd-county 
        admin.sfd-county-rc 
        admin.sfd-emp 
        admin.sfd-emp-rc 
        admin.sfd-multisingle 
        admin.sfd-multisingle-rc 
        admin.sfd-naics 
        admin.sfd-naics-rc 
        admin.sfd-notify-yr 
        admin.sfd-pla 
        admin.sfd-pla-rc 
        admin.sfd-review-yr 
        admin.sfd-wage 
        admin.sfd-wage-rc 
        admin.skip-err 
        admin.spec_ind 
        admin.state_use 
        admin.succ-source 
        admin.succnum 
        admin.succrun 
        admin.taxst 
        admin.tax_cty 
        admin.tax_ext 
        admin.tax_str 
        admin.tax_str2 
        admin.tax_zip 
        admin.third-agent 
        admin.ui_addr_ind 
        admin.unum 
        admin.update-dte 
        admin.website FORMAT "X(35)" WITH 1 COLUMN.
END.

Exercise 3 (tottax.p)
Create a program that will read the quarterly table for active masters that have taxable wages greater than 0 in 2004 and calculate the TOTAL taxable wages for each quarter (use the index “yqur”).  Display the quarter, unum, rnum and taxable wages for each record with a total for taxable wages at the end of each quarter.  Save the program as “tottax.p”.
Solution:
FOR EACH quarterly USE-INDEX yqur WHERE yr = 2004 NO-LOCK BREAK BY qtr:
    IF status_cd = "1" and meei_cd = 2 and tax_wg > 0 THEN
        DISPLAY qtr unum rnum tax_wg(TOTAL BY qtr).
END.

Exercise 4 (tottax2.p)
Modify Exercise 3 to use the FIRST-OF and/or LAST-OF function(s).

FOR EACH quarterly USE-INDEX yqur WHERE yr = 2004 and status_cd = "1" 
                                  and meei_cd = 2 and tax_wg > 0 NO-LOCK BREAK BY qtr:
    IF LAST-OF(qtr) THEN DISPLAY qtr.
    DISPLAY unum rnum tax_wg(TOTAL BY qtr).
END.

Exercise 5 (accumemp.p)
Using the ACCUMULATE and ACCUM functions, create a report that will display the average employment for month 2 of 2004-1 and give a count of the records.  Hint: The aggregations you need are AVERAGE and COUNT.
Solution:
FOR EACH quarterly WHERE yr = 2004 AND qtr = 1 NO-LOCK:
    IF status_cd = "1" THEN 
        ACCUMULATE m2emp (AVERAGE COUNT).
END.
DISPLAY "Average:   " (ACCUM AVERAGE quarterly.m2emp) SKIP(1)
        "# Records: " (ACCUM COUNT quarterly.m2emp) WITH NO-LABELS.

Exercise 6 (exportrecs.p)
Create a program that will export data to a file that will be used as input to a selective run of the EQUI program.  The fields that should be included in the file are state fips code, unum, rnum, year, and quarter.

NOTE: You will need to read the first record in the “state-info” table to find the state fips code.
Solution:
DEF STREAM filstream.
OUTPUT STREAM filstream TO "\win202\equirecs.ot".

FIND FIRST state-info NO-LOCK NO-ERROR.
IF AVAIL state-info THEN DO:
    FOR EACH quarterly WHERE yr = 2003 AND qtr = 1 NO-LOCK:
        IF status_cd = "1" and m3emp > 500 THEN 
        EXPORT STREAM filstream
            state-info.fips unum rnum yr qtr. 
    END.
END.